using System;
using System.Collections.Generic;
using System.Text;
using System.Data.OleDb;
using System.Data;
using System.Configuration;
using System.Windows.Forms;
namespace HostpitalDevice.Business
{
    class DBHelper
    {
        public static OleDbConnection connection;
        public static OleDbCommand command;
        public static OleDbDataReader Reader;
        public static void connectionFactory() 
        {
            if (connection == null)
            {
                String m_Conn = string.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data source={0}\\{1}", Application.StartupPath, ConfigurationManager.ConnectionStrings["DataSource"].ToString());
                connection = new OleDbConnection(m_Conn);
            }
            
        }
        public static DataTable ExecuteDataTable(String sql)
        {
            DataTable result = new DataTable();
            
            try
            {
                connectionFactory();
                if(connection.State==ConnectionState.Closed)
                     connection.Open();
                command  = connection.CreateCommand();
                command.CommandText = sql;
                OleDbDataAdapter adapter = new OleDbDataAdapter(command);
                adapter.Fill(result);
            }
            catch (Exception)
            {
                return null;
            }
            finally
            {
                if (connection.State == ConnectionState.Open)
                    connection.Close();
            }
            return result;
        }
        public static OleDbDataReader ExecuteDataReader(string sql)
        {
            try
            {
                connectionFactory();
                if (connection.State == ConnectionState.Closed)
                    connection.Open();
                command = connection.CreateCommand();
                command.CommandText = sql;
                command.CommandType = CommandType.Text;
                Reader = command.ExecuteReader(CommandBehavior.CloseConnection);

            }
            catch (Exception)
            {
                return null;
            }
            return Reader;
        }
        public static int ExecuteUpdate(String sql)
        {
            try
            {
                connectionFactory();
                if (connection.State == ConnectionState.Closed)
                    connection.Open();
                command = connection.CreateCommand();
                command.CommandText = sql;
               return  command.ExecuteNonQuery();

            }
            catch (Exception)
            {
                return -1;
            }
            finally
            {
                if (connection.State == System.Data.ConnectionState.Open)
                    connection.Close();
                
            }
        }
    }
}
